package exttable.questionnaire;

import dbutils.DbUtils;
import exttable.formgenerator.MessageFactory;
import exttable.formgenerator.StaticMethods;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import utils.Localisation;

import javax.swing.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 * User: alex
 * Date: 28.11.2010
 * Time: 20:03:20
 * To change this template use File | Settings | File Templates.
 */
public class QuestionnaireAnalyser {
    private static final String QUESTION_POINTS = "select  ra.points  from questions q " +
            "    inner join answers a on q.question_id = a.question_id " +
            "    inner join result_answers ra on ra.answer_id = a.answer_id " +
            "where 1 =1 " +
            "    and q.question_symbol in (?) " +
            "    and ra.probationer_id = ? " +
            "    and ra.vizit_id = ? ";

    private Connection conn;
    private double physicalFunctioningFactor;
    private double rolePhysicalFunctioningFactor;
    private double bodilyPainFactor;
    private double generalHealthFactor;
    private double vitalityFactor;
    private double socialFactor;
    private double emotionalFactor;
    private double mentalHealthFactor;

    private static Map<Integer, Double> mapSixDigitRevise = new HashMap<Integer, Double>(6);
    private static Map<Integer, Double> fiveDigitRevise = new HashMap<Integer, Double>(6);

    private List<ViewQuestionnaire> calculatedResult = new ArrayList<ViewQuestionnaire>();
    private ViewQuestionnaire viewQ;
    private String filePath;

    public QuestionnaireAnalyser(String filePath) throws Exception {
        this.conn = DbUtils.getDBConnectionGeneral();
        this.filePath=filePath;
        init();
    }

    private void init() throws SQLException, IOException {
        PreparedStatement ps = conn.prepareStatement("SELECT distinct result_answers.probationer_id,\n" +
                "probationers.probationer_code,\n" +
                "probationers.group_id,\n"+
                "groups.group_code\n" +
                "FROM result_answers\n" +
                "INNER JOIN probationers\n" +
                "ON(result_answers.probationer_id=probationers.probationer_id)\n" +
                "INNER JOIN groups\n" +
                "ON(groups.group_id=probationers.group_id)\n" +
                "ORDER BY probationers.group_id");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            PreparedStatement ps1 = conn.prepareStatement("select distinct vizit_id from result_answers where probationer_id = ?");
            ps1.setInt(1, rs.getInt(1));
            ResultSet rs2 = ps1.executeQuery();
            while (rs2.next()) {
                viewQ = new ViewQuestionnaire();
                viewQ.setProbationerCode(rs.getString(2));
                viewQ.setGroupId(rs.getInt(3));
                viewQ.setGroupCode(rs.getString(4));
                physicalFunctioning(rs.getInt(1), rs2.getInt(1));
            }
        }
        drawExcel();
    }

    private void drawExcel() throws IOException {
        FileOutputStream out = null;
        Cell cell;
        Row row;
        try {
//            File myFolder = new File("C:\\Report");
//            if (myFolder.mkdir()) {
//                System.out.println("success");
//            } else {
//                System.out.println("fail");
//            }
            out = new FileOutputStream(filePath+".xls");
            Workbook wb = new HSSFWorkbook();
            Sheet s = wb.createSheet();
            int rowNum = 1;
            row = s.createRow(0);

            cell = row.createCell(0);
            cell.setCellValue("Group code");

            cell = row.createCell(1);
            cell.setCellValue("Group id");

            cell = row.createCell(2);
            cell.setCellValue("Probationer id");

            cell = row.createCell(3);
            cell.setCellValue("Probationer code");

            cell = row.createCell(4);
            cell.setCellValue("Visit");

            cell = row.createCell(5);
            cell.setCellValue("Physical Health");

            cell = row.createCell(6);
            cell.setCellValue("Mental Health");
            for (ViewQuestionnaire result : calculatedResult) {
                row = s.createRow(rowNum);

                cell = row.createCell(0);
                cell.setCellValue(result.getGroupCode());

                cell = row.createCell(1);
                cell.setCellValue(result.getGroupId());

                cell = row.createCell(2);
                cell.setCellValue(result.getProbationerId());

                cell = row.createCell(3);
                cell.setCellValue(result.getProbationerCode());

                cell = row.createCell(4);
                cell.setCellValue(result.getVisitId());

                cell = row.createCell(5);
                cell.setCellValue(result.getPhysicalHealth());

                cell = row.createCell(6);
                cell.setCellValue(result.getPsychologyHealth());
                rowNum++;
            }
            wb.write(out);
            StaticMethods.getFrameByName("RunQuestionnaireAnalyser").dispose();
            out.close();
            JOptionPane.showMessageDialog(null, Localisation.getMessage("info.filecreated") + filePath+".xls",
                    Localisation.getMessage("info.title"), JOptionPane.INFORMATION_MESSAGE);
        }
        catch (java.io.FileNotFoundException fileOpen) {
            MessageFactory.errorToSave();

        }

        catch (IOException e) {
            e.printStackTrace();
            throw new IOException("error");
        }

        finally {
            out.close();
        }

    }

    private void physicalFunctioning(int probationerID, int visitID) throws SQLException {
//        Суммируйте баллы, полученные при ответах на вопросы: 3a, 3б, 3в, 3г, 3д, 3е, 3ж, 3з, 3и, 3к
//        Полученный суммарный балл пересчитайте по следующему ключу: PF = ( (PFsum – 10)/ 20) * 100

        PreparedStatement ps = conn.prepareStatement("select  ((sum(ra.points) - 10)/20)*100  from questions q" +
                "    inner join answers a on q.question_id = a.question_id" +
                "    inner join result_answers ra on ra.answer_id = a.answer_id" +
                " where 1 =1 " +
                "    and q.question_symbol in ('3a', '3b', '3v','3g','3d','3e', '3j', '3z', '3i', '3k')" +
                "   and ra.probationer_id = ? " +
                "   and ra.vizit_id = ?" +
                "");
        ps.setInt(1, probationerID);
        ps.setInt(2, visitID);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            physicalFunctioningFactor = rs.getDouble(1);
        }
        rolePhysicalFunctioning(probationerID, visitID);
    }

    private void rolePhysicalFunctioning(int probationerID, int visitID) throws SQLException {
//         Суммируйте баллы, полученные при ответах на вопросы: 4а, 4б, 4в, 4г
//         Полученный суммарный балл пересчитайте по следующему ключу:

        PreparedStatement ps = conn.prepareStatement("select  ((sum(ra.points) - 4)/4)*100  from questions q" +
                "    inner join answers a on q.question_id = a.question_id" +
                "    inner join result_answers ra on ra.answer_id = a.answer_id" +
                " where 1 =1 " +
                "    and q.question_symbol in ('4a', '4b', '4v','4g') " +
                "    and ra.probationer_id = ? " +
                "   and ra.vizit_id = ?");
        ps.setInt(1, probationerID);
        ps.setInt(2, visitID);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            rolePhysicalFunctioningFactor = rs.getDouble(1);
        }
        bodilyPain(probationerID, visitID);
    }


    private void bodilyPain(int probationerID, int visitID) throws SQLException {
// значение по шкале интенсиность боли

        double sevenTotalSum = 0;
        double eightTotalSum = 0;

        PreparedStatement ps;
        ResultSet rs;
        Map<Integer, Double> map7Question = new HashMap<Integer, Double>(6);
        map7Question.put(1, 6.0);
        map7Question.put(2, 5.4);
        map7Question.put(3, 4.2);
        map7Question.put(4, 3.1);
        map7Question.put(5, 2.2);
        map7Question.put(6, 1.0);


        ps = conn.prepareStatement(QUESTION_POINTS);
        ps.setString(1, "7");
        ps.setInt(2, probationerID);
        ps.setInt(3, visitID);
        rs = ps.executeQuery();
        while (rs.next()) {
            sevenTotalSum += mapAnalyser(rs.getInt(1), map7Question);
        }

        Map<Integer, Double> map8Question = new HashMap<Integer, Double>(6);
//        System.out.println("sevenTotalSum: " + sevenTotalSum);
        if (sevenTotalSum == 1) {
            map8Question.put(1, 6.0);
        } else if (sevenTotalSum >= 2 & sevenTotalSum <= 6) {
            map8Question.put(1, 5.0);
        }

        map8Question.put(2, 4.0);
        map8Question.put(3, 3.0);
        map8Question.put(4, 2.0);
        map8Question.put(5, 1.0);


        ps = conn.prepareStatement(QUESTION_POINTS);
        ps.setString(1, "8");
        ps.setInt(2, probationerID);
        ps.setInt(3, visitID);
        rs = ps.executeQuery();
        while (rs.next()) {
            eightTotalSum += mapAnalyser(rs.getInt(1), map8Question);
        }


        bodilyPainFactor = (((sevenTotalSum + eightTotalSum) - 2) / 10) * 100;

        generalHealth(probationerID, visitID);

    }


    private void generalHealth(int probationerID, int visitID) throws SQLException {
// Значение по шкале «Общее состояние здоровья (General Health - GH)»
        double firstTotalSum = 0;
        double elevenASum;
        double elevenBSum;
        double elevenVSum;
        double elevenGSum;

        PreparedStatement ps;
        Map<Integer, Double> map1Question = new HashMap<Integer, Double>(5);
        map1Question.put(1, 5.0);
        map1Question.put(2, 4.4);
        map1Question.put(3, 3.4);
        map1Question.put(4, 2.0);
        map1Question.put(5, 1.0);


        ps = conn.prepareStatement(QUESTION_POINTS);
        ps.setString(1, "1");
        ps.setInt(2, probationerID);
        ps.setInt(3, visitID);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            firstTotalSum += mapAnalyser(rs.getInt(1), map1Question);
        }

        elevenBSum = reviseQuestionRunner("11b", fiveDigitRevise, probationerID, visitID);
        elevenGSum = reviseQuestionRunner("11g", fiveDigitRevise, probationerID, visitID);

        elevenASum = straightSumCount("11a", probationerID, visitID);
        elevenVSum = straightSumCount("11v", probationerID, visitID);

        generalHealthFactor = ((firstTotalSum + elevenASum + elevenBSum + elevenVSum + elevenGSum - 5) / 20) * 100;
        VitalityVT(probationerID, visitID);

    }


    private void VitalityVT(int probationerID, int visitID) throws SQLException {
// Значение по шкале «Жизненная активность
        double nineASum;
        double nineDSum;
        double nineZHSum;
        double nineISum;

        nineASum = reviseQuestionRunner("9a", mapSixDigitRevise, probationerID, visitID);
        nineDSum = reviseQuestionRunner("9d", mapSixDigitRevise, probationerID, visitID);

        nineZHSum = straightSumCount("9j", probationerID, visitID);
        nineISum = straightSumCount("9i", probationerID, visitID);

        vitalityFactor = ((nineASum + nineDSum + nineZHSum + nineISum - 4) / 20) * 100;
        socialFunctioning(probationerID, visitID);
    }


    private void socialFunctioning(int probationerID, int visitID) throws SQLException {
        // Значение по шкале Социальное функционирование (Social Functioning - SF)»
        double sixSum;
        double tenSum;
        sixSum = reviseQuestionRunner("6", fiveDigitRevise, probationerID, visitID);
//        tenSum = reviseQuestionRunner("10", mapSixDigitRevise, probationerID, visitID);
        tenSum = straightSumCount("10", probationerID, visitID);
        socialFactor = ((sixSum + tenSum - 2) / 8) * 100;
        roleEmotional(probationerID, visitID);

    }

    private void roleEmotional(int probationerID, int visitID) throws SQLException {
//  Значение по шкале «Ролевое функционирование, обусловленное эмоциональным состоянием (Role-Emotional - RE)»

        PreparedStatement ps = conn.prepareStatement("select  ((sum(ra.points) - 4)/4)*100  from questions q" +
                "    inner join answers a on q.question_id = a.question_id" +
                "    inner join result_answers ra on ra.answer_id = a.answer_id" +
                " where 1 =1 " +
                "    and q.question_symbol in ('4a', '4b', '4v','4g') " +
                "    and ra.probationer_id = ? " +
                "   and ra.vizit_id = ?");
        ps.setInt(1, probationerID);
        ps.setInt(2, visitID);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            emotionalFactor = rs.getDouble(1);
        }
        mentalHealth(probationerID, visitID);


    }


    private void mentalHealth(int probationerId, int visitID) throws SQLException {
        double nineGSum;
        double nineZSum;

        double nineBSum;
        double nineVSum;
        double nineESum;

        nineGSum = reviseQuestionRunner("9g", mapSixDigitRevise, probationerId, visitID);
        nineZSum = reviseQuestionRunner("9z", mapSixDigitRevise, probationerId, visitID);

        nineBSum = straightSumCount("9b", probationerId, visitID);
        nineVSum = straightSumCount("9v", probationerId, visitID);
        nineESum = straightSumCount("9e", probationerId, visitID);

        mentalHealthFactor = ((nineGSum + nineZSum + nineBSum + nineVSum + nineESum - 5) / 25) * 100;

        viewQ.setProbationerId(probationerId);
        viewQ.setVisitId(visitID);
        PHCalculator(viewQ);
        MHCalculator(viewQ);
        calculatedResult.add(viewQ);

    }

    private void MHCalculator(ViewQuestionnaire viewQ) {
//        System.out.println("PF : " + physicalFunctioningFactor + " p " + viewQ.getProbationerId() + " v " + viewQ.getVisitId());
//        System.out.println("RP : " + rolePhysicalFunctioningFactor + " p " + viewQ.getProbationerId() + " v " + viewQ.getVisitId());
//        System.out.println("BP : " + bodilyPainFactor + " p " + viewQ.getProbationerId() + " v " + viewQ.getVisitId());
//        System.out.println("GH : " + generalHealthFactor + " p " + viewQ.getProbationerId() + " v " + viewQ.getVisitId());
//        System.out.println("VT : " + vitalityFactor + " p " + viewQ.getProbationerId() + " v " + viewQ.getVisitId());
//        System.out.println("SF : " + socialFactor + " p " + viewQ.getProbationerId() + " v " + viewQ.getVisitId());
//        System.out.println("RE : " + emotionalFactor + " p " + viewQ.getProbationerId() + " v " + viewQ.getVisitId());
//        System.out.println("MH : " + mentalHealthFactor + " p " + viewQ.getProbationerId() + " v " + viewQ.getVisitId());

        double Mhsum = ((physicalFunctioningFactor - 84.52404) / 22.89490 * -0.22999) +
                ((rolePhysicalFunctioningFactor - 81.19907) / 33.797290 * -0.12329) +
                ((bodilyPainFactor - 75.49196) / 23.558790 * -0.09731) +
                (socialFactor * 0.26876) +
                ((mentalHealthFactor - 74.84212) / 18.01189 * 0.48581) +
                ((emotionalFactor - 81.29467) / 33.02717 * 0.43407) +
                ((vitalityFactor - 61.05453) / 20.86942 * 0.23534) +
                ((generalHealthFactor - 72.21316) / 20.16964 * -0.01571);
        double PH = (Mhsum * 10) + 50;
//        System.out.println("MHPH " + PH);
        viewQ.setPsychologyHealth(PH);
    }

    private void PHCalculator(ViewQuestionnaire viewQ) {

        double PHsum = ((physicalFunctioningFactor - 84.52404) / 22.89490 * 0.42402) +
                ((rolePhysicalFunctioningFactor - 81.19907) / 33.797290 * 0.35119) +
                ((bodilyPainFactor - 75.49196) / 23.558790 * 0.31754) +
                ((socialFactor - 8359753) / 2237642 * -0.00753) +
                ((mentalHealthFactor - 74.84212) / 18.01189 * -0.22069) +
                ((emotionalFactor - 81.29467) / 33.02717 * -0.19206) +
                ((vitalityFactor - 61.05453) / 20.86942 * -0.02877) +
                ((generalHealthFactor - 72.21316) / 20.16964 * -0.24954);
//        System.out.println("physicalFunctioningFactor : " + (physicalFunctioningFactor - 84.52404) / 22.89490);
//        System.out.println("rolePhysicalFunctioningFactor " + (rolePhysicalFunctioningFactor - 81.19907) / 33.797290);
//        System.out.println("bodilyPainFactor " + (bodilyPainFactor - 75.49196) / 23.558790);
//        System.out.println("vitalityFactor : " + (vitalityFactor - 61.05453) / 20.86942);
//        System.out.println("mentalHealthFactor " + (mentalHealthFactor - 74.84212) / 18.01189);
//        System.out.println("emotionalFactor " + (emotionalFactor - 81.29467) / 33.02717);
//        System.out.println("generalHealthFactor " + (generalHealthFactor - 72.21316) / 20.16964);
        double PH = (PHsum * 10) + 50;
//        System.out.println("PH " + PH);
        viewQ.setPhysicalHealth(PH);

    }


    private double reviseQuestionRunner(String questionSymbol, Map<Integer, Double> mapPointRecount, int probationerId, int visitID) throws SQLException {
        double toReturn = 0;
        PreparedStatement ps = conn.prepareStatement(QUESTION_POINTS);
        ps.setString(1, questionSymbol);
        ps.setInt(2, probationerId);
        ps.setInt(3, visitID);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            toReturn = toReturn + mapAnalyser(rs.getInt(1), mapPointRecount);
        }
        return toReturn;

    }

    private double mapAnalyser(int rowPoint, Map<Integer, Double> mapPointRecount) {
        for (Map.Entry<Integer, Double> res : mapPointRecount.entrySet()) {
            if (rowPoint == res.getKey()) {
                return res.getValue();
            }
        }
        throw new IllegalArgumentException("no such value");
    }

    private int straightSumCount(String questionSymbol, int probationId, int visitId) throws SQLException {
        int toReturn = 0;
        PreparedStatement ps = conn.prepareStatement(QUESTION_POINTS);
        ps.setString(1, questionSymbol);
        ps.setInt(2, probationId);
        ps.setInt(3, visitId);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            toReturn = toReturn + rs.getInt(1);
        }
        return toReturn;
    }

//    public static void main(String[] args) throws Exception {
//
//        new QuestionnaireAnalyser();
//    }

    static {
        mapSixDigitRevise.put(1, 6.0);
        mapSixDigitRevise.put(2, 5.0);
        mapSixDigitRevise.put(3, 4.0);
        mapSixDigitRevise.put(4, 3.0);
        mapSixDigitRevise.put(5, 2.0);
        mapSixDigitRevise.put(6, 1.0);

        fiveDigitRevise.put(1, 5.0);
        fiveDigitRevise.put(2, 4.0);
        fiveDigitRevise.put(3, 3.0);
        fiveDigitRevise.put(4, 2.0);
        fiveDigitRevise.put(5, 1.0);
    }
}

